import base64

import oracledb as odb
import pandas as pd

from Crypto.Cipher import AES
from Crypto.Util.Padding import unpad

db_dict = {
    'dsn':'192.168.123.65:1521/orcl',
    'user':'MIP_USER_NEW',
    'password':'MIPUSER123'
}

table = 'DICT_CIS_ICD9'

# 瘦模式连接 Oracle
conn = odb.connect(user=db_dict['user'], password=db_dict['password'], dsn=db_dict['dsn'])

# 查询 dict_cis_icd10 并将字段注释射程标题
cursor = conn.cursor()
cursor.execute(f'''
    SELECT column_name, comments
    FROM user_col_comments
    WHERE table_name = '{table}'
''')
# 转为字段名和注释的映射字典
column_names = {row[0]: row[1] for row in cursor.fetchall()}
# 读取数据
df = pd.read_sql(f"select * from {table}",conn)
# 将列名替换按column_names转换为注释
df.columns = [column_names.get(col, col) for col in df.columns]
# ICD10
# de = (
#     '章内涵','章包括','章不包括','章备注',
# '节内涵','节包括','节不包括','节备注',
# '亚目内涵','亚目包括','亚目不包括','亚目备注',
# '疾病内涵','疾病包括','疾病不包括','疾病备注'
# )

# ICD9
de = (
    '章内涵','章包括','章不包括','章备注',
'亚目内涵','亚目包括','亚目不包括','亚目备注',
'细目内涵','细目包括','细目不包括','细目备注',
'手术内涵','手术包括','手术不包括','手术备注'
)

# AES解密函数
def aes_decrypt(ciphertext, key):
    try:
        cipher = AES.new(key, AES.MODE_ECB)
        decrypted = cipher.decrypt(base64.b64decode(ciphertext))
        return unpad(decrypted, AES.block_size).decode('utf-8')
    except (ValueError, Exception) as e:
        print(f"解密错误: {e}")
        return ciphertext  # 返回原始值

# 使用用户提供的AES-128密钥
key = b'c6cbde5f05d357ac'  # 16字节的密钥

# 对de中的字段进行解密
for col in de:
    if col in df.columns and pd.notna(df[col]).any():
        df[col] = df[col].apply(lambda x: aes_decrypt(x, key) if pd.notna(x) else x)

df.to_excel('dict_cis_icd9.xlsx',index=False)
print('导出完成！')
conn.close()